<!doctype html>



  


<html class="theme-next mist use-motion">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>



<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />












  
  
  <link href="/vendors/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  

  

  

  

  
    
    
    <link href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






<link href="/vendors/font-awesome/css/font-awesome.min.css?v=4.4.0" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.0.1" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="索引," />








  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.0.1" />






<meta name="description" content="说明：数据库的索引优化。">
<meta name="keywords" content="索引">
<meta property="og:type" content="article">
<meta property="og:title" content="索引优化">
<meta property="og:url" content="http://bebetter.site/2017/09/01/Database/SQL-advanced/索引优化/index.html">
<meta property="og:site_name" content="gatewayzy">
<meta property="og:description" content="说明：数据库的索引优化。">
<meta property="og:updated_time" content="2017-09-01T05:58:10.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="索引优化">
<meta name="twitter:description" content="说明：数据库的索引优化。">



<script type="text/javascript" id="hexo.configuration">
  var NexT = window.NexT || {};
  var CONFIG = {
    scheme: 'Mist',
    sidebar: {"position":"right","display":"always"},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: 0,
      author: '博主'
    }
  };
</script>




  <link rel="canonical" href="http://bebetter.site/2017/09/01/Database/SQL-advanced/索引优化/"/>

  <title> 索引优化 | gatewayzy </title>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  










  
  
    
  

  <div class="container one-collumn sidebar-position-right page-post-detail ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-meta ">
  

  <div class="custom-logo-site-title">
    <a href="/"  class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <span class="site-title">gatewayzy</span>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>
  <p class="site-subtitle">blog of gatewayzy</p>
</div>

<div class="site-nav-toggle">
  <button>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
  </button>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      

      
    </ul>
  

  
</nav>

 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                索引优化
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2017-09-01T13:58:10+08:00" content="2017-09-01">
              2017-09-01
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/Database/" itemprop="url" rel="index">
                    <span itemprop="name">Database</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
          

          

          
          

          
        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        <p><strong>说明：</strong>数据库的索引优化。<br><a id="more"></a></p>
<ul>
<li>数据库方面的技能要求，包括如下层面：<ul>
<li>基本概念和语句使用，包括：增删改查CRUD、存储过程、事务……</li>
<li>设计表的经验，包括：引擎、字段、范式……关键是要结合实际需求。</li>
<li>数据库优化的经验，包括：索引、缓存、读写分离、冗余……</li>
<li>分布式数据库与集群。</li>
</ul>
</li>
</ul>
<h2 id="索引概述"><a href="#索引概述" class="headerlink" title="索引概述"></a>索引概述</h2><ul>
<li>mysql联合索引<ul>
<li>索引的特性是：索引有序、高度较低、存储列值，联合索引对多个列建立索引表，查询的时候先进行第一列关键字查找，然后进行第二列关键字查找，类推然后返回结果。优点在于如果多个列同时限定的记录比较少就能建立联合索引减少数据量，以及如果将信息与查找值在一起就可以避免再次回表读取数据。但是联合索引的缺点是插入删除时需要更新索引，而建索引会锁定整张表，如果联合索引的列数超过3就应该考虑重新设计表，如果需要大量更新可以先删除索引再重建索引以提高效率。</li>
<li>数据库联合索引，如果a(1-100)，b(1-3)，c(1-1000)的范围该怎么建。</li>
</ul>
</li>
<li>索引结构：索引用BTree B树数据结构。</li>
<li><p>索引优缺点</p>
<ul>
<li>优点：快速检索</li>
<li>缺点：占用额外磁盘空间、插入新数据需要重新构建索引。</li>
</ul>
</li>
<li><p>是否需要使用索引的场景：</p>
<ul>
<li>场景一，数据表规模不大，就几千行，即使不建索引，查询语句的返回时间也不长，这时建索引的意义就不大。当然，若就几千行，索引所占的空间也不多，所以这种情况下，顶多属于“性价比”不高。</li>
<li>场景二，某个商品表里有几百万条商品信息，同时每天会在一个时间点，往其中更新大概十万条左右的商品信息，现在用where语句查询特定商品时（比如wherename=‘XXX’）速度很慢。为了提升查询效率可以建索引，但当每天更新数据时，又会重建索引，这是要耗费时间的。这时就需要综合考虑，甚至可以在更新前删除索引，更新后再重建。</li>
<li>场景三，如果在数据表里ID值都不相同，索引能发挥出比较大的作用。相反，如果某个字段重复率很高，如性别字段，或者某个字段大多数值是空（null），那么不建议对该字段建索引。</li>
</ul>
</li>
<li><p>sql是否正确利用了索引：</p>
<ul>
<li><code>select * from 商品表</code>，<strong>没有where语句，不会用到索引</strong>。如果有 where name=‘Java书’，会使用索引。当然如果数据量不多，或者name没有太多区分度，那么为name建索引也不会有太多好处。</li>
<li><code>select * from 商品表 where name like ‘Java%’</code>， 这个模糊查询会用到索引。但是where name like ‘%java’，不会走索引。用like进行模糊查询时，<strong>如果第一个是模糊的匹配符，就不会走索引，但只要不出现在第一个位置，不论用了多少个%，也不论%的位置，那么都能用到索引</strong>。</li>
<li><code>select * from 成绩表 where 成绩&gt;95</code>，使用大于号，不能用到索引。为了用到索引，可以改为：where成绩in(96,97,98,99,100)。<strong>当数值型的字段遇到非等值操作符时，无法使用索引</strong>。</li>
<li><code>select * from 成绩表 where score +40 = 100</code>，<strong>对索引字段进行了某种左值操作，无法用到索引。</strong>可以改为where score = 60可以使用索引。</li>
<li><code>select * from 商品表 where substr(name)=‘J’</code>，<strong>对索引字段进行了函数操作，无法用到索引。</strong>可以改成where name like ‘%J’，也可以对substr(name)建索引。</li>
</ul>
</li>
</ul>

      
    </div>

    <div>
      
        

      
    </div>

    <div>
      
        

      
    </div>

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/索引/" rel="tag">#索引</a>
          
        </div>
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2017/09/01/Database/SQL-advanced/高性能MySQL/" rel="next" title="高性能MySQL">
                <i class="fa fa-chevron-left"></i> 高性能MySQL
              </a>
            
          </div>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2017/09/01/Database/SQL-basic/SQL基本知识/" rel="prev" title="SQL基本知识">
                SQL基本知识 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          
  <div class="comments" id="comments">
    


  </div>


        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap" >
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview sidebar-panel ">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/statics/images/avatar.png"
               alt="gatewayzy" />
          <p class="site-author-name" itemprop="name">gatewayzy</p>
          <p class="site-description motion-element" itemprop="description">blog website with hexo and github pages</p>
        </div>
        <nav class="site-state motion-element">
          <div class="site-state-item site-state-posts">
            <a href="/archives">
              <span class="site-state-item-count">70</span>
              <span class="site-state-item-name">日志</span>
            </a>
          </div>

          
            <div class="site-state-item site-state-categories">
              <a href="/categories">
                <span class="site-state-item-count">10</span>
                <span class="site-state-item-name">分类</span>
              </a>
            </div>
          

          
            <div class="site-state-item site-state-tags">
              <a href="/tags">
                <span class="site-state-item-count">38</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        

        <div class="links-of-author motion-element">
          
        </div>

        
        

        
        
          <div class="links-of-blogroll motion-element links-of-blogroll-inline">
            <div class="links-of-blogroll-title">
              <i class="fa  fa-fw fa-globe"></i>
              友情链接
            </div>
            <ul class="links-of-blogroll-list">
              
                <li class="links-of-blogroll-item">
                  <a href="https://github.com/gatewayzy" title="Github-gatewayzy" target="_blank">Github-gatewayzy</a>
                </li>
              
                <li class="links-of-blogroll-item">
                  <a href="http://google.com/" title="Goolge" target="_blank">Goolge</a>
                </li>
              
                <li class="links-of-blogroll-item">
                  <a href="http://wiki.jikexueyuan.com/" title="Wiki-jike" target="_blank">Wiki-jike</a>
                </li>
              
            </ul>
          </div>
        

      </section>

      
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">
            
              
            
            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#索引概述"><span class="nav-number">1.</span> <span class="nav-text">索引概述</span></a></li></ol></div>
            
          </div>
        </section>
      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy; 
  <span itemprop="copyrightYear">2018</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">gatewayzy</span>
</div>

<div class="powered-by">
  由 <a class="theme-link" href="https://hexo.io">Hexo</a> 强力驱动
</div>

<div class="theme-info">
  主题 -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next">
    NexT.Mist
  </a>
</div>

        

        
      </div>
    </footer>

    <div class="back-to-top">
      <i class="fa fa-arrow-up"></i>
    </div>
  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  



  
  <script type="text/javascript" src="/vendors/jquery/index.js?v=2.1.3"></script>

  
  <script type="text/javascript" src="/vendors/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  
  <script type="text/javascript" src="/vendors/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  
  <script type="text/javascript" src="/vendors/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/vendors/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/vendors/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.0.1"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.0.1"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.0.1"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.0.1"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.0.1"></script>



  



  




  
  

  
  <script type="text/x-mathjax-config">
    MathJax.Hub.Config({
      tex2jax: {
        inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
        processEscapes: true,
        skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
    });
  </script>

  <script type="text/x-mathjax-config">
    MathJax.Hub.Queue(function() {
      var all = MathJax.Hub.getAllJax(), i;
      for (i=0; i < all.length; i += 1) {
        all[i].SourceElement().parentNode.className += ' has-jax';
      }
    });
  </script>
  <script type="text/javascript" src="//cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>



  

  
<script type="text/javascript" async src="//push.zhanzhang.baidu.com/push.js">
</script>


</body>
</html>
